package com.example.database.library;

import java.util.HashMap;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHandler extends SQLiteOpenHelper {

	private static final int DATABASE_VERSION = 1;

	// Database Name
	private static final String DATABASE_NAME = "android_api";

	// Login table name
	private static final String TABLE_LOGIN = "login";
	
	// Book table name
	private static final String TABLE_BOOK = "book";
	
	// Login Table Columns names
	private static final String KEY_ID = "id";
	private static final String KEY_NAME = "name";
	private static final String KEY_EMAIL = "email";
	private static final String KEY_UID = "uid";
	private static final String KEY_CREATED_AT = "created_at";

	// Book Table Columns names
	private static final String KEY1_ID = "id";
	private static final String KEY1_TITLE = "title";
	private static final String KEY1_BRIEF = "brief";
	private static final String KEY1_PRESS = "press";
	private static final String KEY1_META = "meta";
	private static final String KEY1_PUBLISHED = "publishedYear";
	private static final String KEY1_BESTSELLER = "bestseller";
	private static final String KEY1_COVER = "cover";
	private static final String KEY1_RATING = "rating";
	private static final String KEY1_PRICE = "PRICE";
	private static final String KEY1_LASTVIEWDATE = "lastViewDate";
	private static final String KEY1_isDraft = "isDraft";
	private static final String KEY1_viewCount = "viewCount";
	private static final String KEY1_soldCount = "soldCount";
	private static final String KEY1_isbnCode = "isbnCode";
	private static final String KEY1_pageCount = "pageCount";
	private static final String KEY1_language = "language";
	private static final String KEY1_category_id = "id";
	private static final String KEY1_category_name = "name";
	private static final String KEY1_tags = "tag";
	
	public DatabaseHandler(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	// Creating Tables
	@Override
	public void onCreate(SQLiteDatabase db) {
		String CREATE_LOGIN_TABLE = "CREATE TABLE " + TABLE_LOGIN + "("
				+ KEY_ID + " INTEGER PRIMARY KEY," 
				+ KEY_NAME + " TEXT,"
				+ KEY_EMAIL + " TEXT UNIQUE,"
				+ KEY_UID + " TEXT,"
				+ KEY_CREATED_AT + " TEXT" + ")";
		db.execSQL(CREATE_LOGIN_TABLE);
		String CREATE_BOOK_TABLE = "CREATE TABLE " + TABLE_BOOK + "("
				+ KEY1_ID + " INTEGER PRIMARY KEY," 
				+ KEY1_TITLE + " TEXT,"
				+ KEY1_BRIEF + " TEXT ,"
				+ KEY1_PRESS + " TEXT,"
				+ KEY1_META + " TEXT,"
				+ KEY1_PUBLISHED + " INTEGER,"
				+ KEY1_BESTSELLER + " BOOLEAN,"
				+ KEY1_COVER + " TEXT,"
				+ KEY1_RATING + " INTEGER,"
				+ KEY1_PRICE + " INTEGER,"
				+ KEY1_LASTVIEWDATE + " DATE,"
				+ KEY1_isDraft + " INTEGER,"
				+ KEY1_viewCount + " INTEGER,"
				+ KEY1_soldCount + " INTEGER,"
				+ KEY1_isbnCode + " TEXT,"
				+ KEY1_pageCount + " INTEGER,"
				+ KEY1_language + " TEXT,"
				+ KEY1_category_id + " INTEGER," 
				+ KEY1_category_name + " TEXT,"
				+ KEY1_tags + " INTEGER," + ")";
		db.execSQL(CREATE_BOOK_TABLE);
		
	}

	// Upgrading database
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Drop older table if existed
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_LOGIN);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_BOOK);
		// Create tables again
		onCreate(db);
	}


	public void addUser(String name, String email, String uid, String created_at) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_NAME, name); // ner
		values.put(KEY_EMAIL, email); // Email
		values.put(KEY_UID, uid); // Email
		values.put(KEY_CREATED_AT, created_at); // Created At

		// Inserting Row
		db.insert(TABLE_LOGIN, null, values);
		db.close(); // Closing database connection
	}
	
	public void addBook(String name, String author, int published, int rating, String Desc, int Pages, int Category, String url, int Price) {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(KEY1_TITLE, name); 
		values.put(KEY1_BRIEF, author); 
		values.put(KEY1_PUBLISHED, published); 
		values.put(KEY1_RATING, rating); 
		values.put(KEY1_META, Desc); 
		values.put(KEY1_PUBLISHED, Pages); 
		values.put(KEY1_LASTVIEWDATE, Category); 
		values.put(KEY1_URL, url); 
		values.put(KEY1_PRICE, Price); 
		values.put(KEY1_PRICE, Price);
		values.put(KEY1_PRICE, Price);
		values.put(KEY1_PRICE, Price);
		values.put(KEY1_PRICE, Price);
		values.put(KEY1_PRICE, Price);
		values.put(KEY1_PRICE, Price);
		values.put(KEY1_PRICE, Price);
		values.put(KEY1_PRICE, Price);
		values.put(KEY1_PRICE, Price);
		values.put(KEY1_PRICE, Price);
		values.put(KEY1_PRICE, Price);
		values.put(KEY1_PRICE, Price);
		values.put(KEY1_PRICE, Price);
		// Inserting Row
		db.insert(TABLE_BOOK, null , values); 
		db.close(); //Closing database connection
	} 
	
	/**
	 * Getting user data from database
	 * */
	public HashMap<String, String> getUserDetails(){
		HashMap<String,String> user = new HashMap<String,String>();
		String selectQuery = "SELECT  * FROM " + TABLE_LOGIN;
		 
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        // Move to first row
        cursor.moveToFirst();
        if(cursor.getCount() > 0){
        	user.put("name", cursor.getString(1));
        	user.put("email", cursor.getString(2));
        	user.put("uid", cursor.getString(3));
        	user.put("created_at", cursor.getString(4));
        }
        cursor.close();
        db.close();
		// return user
		return user;
	}
	
	/**
	 * Getting user data from database
	 * */
	public HashMap<String, String> getBookDetails(){
		HashMap<String,String> book = new HashMap<String,String>();
		String selectQuery = "SELECT  * FROM " + TABLE_LOGIN;
		 
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        // Move to first row
        cursor.moveToFirst();
        if(cursor.getCount() > 0){
        	book.put("na", cursor.getString(1));
        	book.put("au", cursor.getString(2));
        	book.put("pu", cursor.getString(3));
        	book.put("ra", cursor.getString(4));
        	book.put("de", cursor.getString(5));
        	book.put("pa", cursor.getString(6));
        	book.put("ca", cursor.getString(7));
        	book.put("ur", cursor.getString(8));
        	book.put("pr", cursor.getString(9));
        	
        }
        cursor.close();
        db.close();
		// return book
		return book;
	}
	
	public int getRowCountB() {
		String countQuery = "SELECT * FROM " + TABLE_BOOK;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);
		int rowCount = cursor.getCount();
		db.close();
		cursor.close();
		
		//return row count
		return rowCount;
	}
	
	public int getRowCount() {
		String countQuery = "SELECT  * FROM " + TABLE_LOGIN;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);
		int rowCount = cursor.getCount();
		db.close();
		cursor.close();
		
		// return row count
		return rowCount;
	}
	
	/**
	 * Re crate database
	 * Delete all tables and create them again
	 * */
	public void resetTables(){
		SQLiteDatabase db = this.getWritableDatabase();
		// Delete All Rows
		db.delete(TABLE_LOGIN, null, null);
		db.close();
	}
	

}
